/**
 * 
 */
package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.entity.expert.AuthIinspectionOpinions;

/**
 * @author gaogs
 *
 * 2016-12-5
 */
@Repository
public class AuthIinspectionOpinionsRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthIinspectionOpinionsDao authIinspectionOpinionsDao;
	
	/**
	 * 保存现场考察一级指标分项考察意见表
	 * @param list
	 */
	public void saveAuthIinspectionOpinionsInfo(List<AuthIinspectionOpinions> list){
		authIinspectionOpinionsDao.save(list);
	}
	
	
	/**
	 * 查询专家某一级指标分项考察意见表
	 * @param groupId
	 * @param itemId
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthIinspectionOpinions> queryAuthIinspectionOpinionsLists(String groupId ,String itemId,String expertId) throws SQLException{
		String sql="SELECT mm.p_id as pid ,mm.s_group_id as groupId ,mm.s_expert_id as expertId ,mm.s_item_id as itemId ," +
				" mm.s_key as keys ,mm.s_value as value ,mm.s_create_time as createTime ,mm.s_mode as mode " +
				" FROM t_auth_inspection_opinions as mm WHERE mm.s_group_id=? AND mm.s_item_id=? AND mm.s_expert_id=?" ;
		List<AuthIinspectionOpinions> list=dbHelper.getBeanList(sql, AuthIinspectionOpinions.class, groupId,itemId,expertId);
		return list;
	}
	
	/**
	 * 查询专家某一级指标分项考察意见表
	 * @param groupId
	 * @param itemId
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthIinspectionOpinions queryAuthIinspectionOpinionsLists(String groupId ,String expertId) throws SQLException{
		String sql="SELECT TOP 1 mm.p_id as pid ,mm.s_group_id as groupId ,mm.s_expert_id as expertId ,mm.s_item_id as itemId ," +
				" mm.s_key as keys ,mm.s_value as value ,mm.s_create_time as createTime ,mm.s_mode as mode " +
				" FROM t_auth_inspection_opinions as mm WHERE mm.s_group_id=?  AND mm.s_expert_id=?" ;
		AuthIinspectionOpinions list=dbHelper.getBean(sql, AuthIinspectionOpinions.class, groupId,expertId);
		return list;
	}
	
	
	/**
	 * 删除某专家一级指标分项考察意见表数据
	 * @param groupId
	 * @param itemId
	 * @param expertId
	 * @throws SQLException
	 */
	public void deleteAuthIinspectionOpinionss(String groupId ,String itemId,String expertId)throws SQLException{
		String sql="DELETE FROM t_auth_inspection_opinions WHERE s_group_id=? AND s_item_id=? AND s_expert_id=?" ;
		dbHelper.execute(sql, groupId,itemId,expertId);
	}
	
	/**
	 * 查询联络员汇总表
	 * @param groupId
	 * @param itemId
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthIinspectionOpinions> queryAuthIinspectionOpinions(String groupId ,String itemId) throws SQLException{
		String sql="SELECT mm.p_id as pid ,mm.s_group_id as groupId ,mm.s_expert_id as expertId ,mm.s_item_id as itemId ," +
				" mm.s_key as keys ,mm.s_value as value ,mm.s_create_time as createTime ,mm.s_mode as mode, " +
				" a.s_content as content ,a.s_number as number " +
				" FROM t_auth_inspection_opinions as mm left join t_auth_item as a on mm.s_item_id = a.p_id WHERE mm.s_group_id=? AND mm.s_item_id=? AND mm.s_mode = 'b'" ;
		List<AuthIinspectionOpinions> list=dbHelper.getBeanList(sql, AuthIinspectionOpinions.class, groupId,itemId);
		return list;
	}
	
	
	
	
	

}
